package example.POI;

import HtServer.RedisTools.RedisToos;
import HtServer.meeting.MeetingItem;
import HtServer.meeting.MeetingItems;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Base64;
import java.util.LinkedList;
import java.util.List;

import static HtServer.meeting.MeetingItems._radonBase64;
import static HtServer.meeting.MeetingItems.getInstance;

public class ImportMeetingMenber {

    String filePath;
    List<MeetingItem> meetingItems = new LinkedList<>();

    public ImportMeetingMenber()
    {

    }
    public ImportMeetingMenber(String filePath)
    {
        this.filePath = filePath;
    }
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        String cellValue = "";
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = cell.getDateCellValue().toString();
                } else {
                    cellValue = Double.toString(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                cellValue = Boolean.toString(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                cellValue = "Type not supported.";
                break;
        }
        return cellValue;
    }


    public void ImportMeetingMenberCells()
    {
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(new File(filePath));
            workbook = new XSSFWorkbook(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            int rownum = -1;
            for (Row row : firstSheet) {

                rownum++;
                if(rownum == 0)
                {

                    continue;
                }


                MeetingItem mi = new MeetingItem();
                String id = getCellValueAsString(row.getCell(0));
                mi.setId(id);
                //名字
                String cellValue = getCellValueAsString(row.getCell(1));
                mi.setName(cellValue);

                String contents = getCellValueAsString(row.getCell(3));
                mi.setContents(contents);

                String dept = getCellValueAsString(row.getCell(4));
                mi.setDept(dept);

                String groupid = getCellValueAsString(row.getCell(6));
                mi.setGroupid(groupid);
                String groupname = getCellValueAsString(row.getCell(7));
                mi.setGroupname(groupname);

                String order = getCellValueAsString(row.getCell(8));
                mi.setOrder(order);


//                System.out.println(POIImages.getInstance().images);
                POIImageItem poiImageItem = POIImages.getInstance().getImages(rownum,2);

                if (poiImageItem != null) {
                    mi.setImage(poiImageItem.getBase64());
                } else {
                    mi.setImage("");
                }


                meetingItems.add(mi);



//                for (Cell cell : row) {
//                    // 获取单元格的值并打印
//                    String cellValue = getCellValueAsString(cell);
//
//
//                    System.out.print(cellValue + "\t");
//                }
//                System.out.println();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (workbook != null) workbook.close();
                if (inputStream != null) inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void MeetingMenberImages() {
        try (FileInputStream fis = new FileInputStream(filePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {

            XSSFSheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            List<XSSFShape> shapes = sheet.createDrawingPatriarch().getShapes();

            for (XSSFShape shape : shapes) {
                if (shape instanceof XSSFPicture) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFPictureData pictureData = picture.getPictureData();
                    ClientAnchor anchor = picture.getClientAnchor();

                    System.out.println("图片类型: " + pictureData.getPictureType());
                    System.out.println("图片格式: " + pictureData.suggestFileExtension());
                    System.out.println("图片位置 - Row1: " + anchor.getRow1() + " Col1: " + anchor.getCol1());

                    byte[] bytes = pictureData.getData();
                    String base64 = Base64.getEncoder().encodeToString(bytes);
                    POIImages.getInstance().addImage(new POIImageItem(anchor.getRow1(), anchor.getCol1(), base64));
                } else {
                    System.out.println("shape: " + shape);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();

        }

    }



    public void writeToExcle(String filepath)
    {
        String excelFilePath = filepath;

        try (XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作簿
             FileOutputStream fos = new FileOutputStream(excelFilePath)) {
            XSSFSheet sheet = workbook.createSheet("Data"); // 创建工作表

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("ID");
            headerRow.createCell(1).setCellValue("人员名字");
            headerRow.createCell(2).setCellValue("人员图片");
            headerRow.createCell(3).setCellValue("内容");
            headerRow.createCell(4).setCellValue("部门");
            headerRow.createCell(5).setCellValue("点赞数量");
            headerRow.createCell(6).setCellValue("组id");
            headerRow.createCell(7).setCellValue("组名");
            headerRow.createCell(8).setCellValue("显示顺序");


            LinkedList<MeetingItem> ll = MeetingItems.getInstance().getLl();
            // 创建绘图工具
            XSSFDrawing drawing = sheet.createDrawingPatriarch();

            // 创建锚点设置图片位置
            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();

            // 填充数据
            for (int i = 0; i < ll.size(); i++) {

                MeetingItem meetingItem = MeetingItems.getInstance().getLl().get(i);
                Row row = sheet.createRow(i+1);
                row.setHeight((short)1024);
                row.createCell(0).setCellValue(i);
                row.createCell(1).setCellValue(meetingItem.getName());
//                row.createCell(2).setCellValue(getInstance()._radonBase64());
                row.createCell(3).setCellValue(meetingItem.getContents());
                row.createCell(4).setCellValue(meetingItem.getDept());
                row.createCell(5).setCellValue(meetingItem.getLike());
                row.createCell(6).setCellValue(meetingItem.getGroupid());
                row.createCell(7).setCellValue(meetingItem.getGroupname());
                row.createCell(8).setCellValue(meetingItem.getOrder());



                // 图片插入的单元格
                anchor.setCol1(2); // 列A
                anchor.setRow1(i); // 行1
                anchor.setCol2(3); // 列B (图片宽度占到第B列)
                anchor.setRow2(i+1); // 行3 (图片高度占到第3行)

                if (meetingItem.getImage() != null && !meetingItem.getImage().isEmpty()) {
                    // 将 Base64 字符串解码为字节数组
                    // (Base64 编码的字符串主要用于通过网络传输文本数据，可以在 HTTP 协议、电子邮件、URL 中使用)
//                    System.out.println("meetingItem.getImage() = " + _radonBase64());
//                    byte[] bytes = Base64.getDecoder().decode(_radonBase64());
                    byte[] bytes = Base64.getDecoder().decode(meetingItem.getImage());
                    // 插入图片
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);
                    picture.resize(); // 自动调整图片大小以适应单元格
                }
//                byte[] bytes = Base64.getDecoder().decode(meetingItem.getImage());
//                // 插入图片
//                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
//                XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);
//                picture.resize(); // 自动调整图片大小以适应单元格

            }

            workbook.write(fos); // 写入文件
            System.out.println("Excel file was created successfully!");
        } catch (IOException e) {
            System.out.println("Error occurred: " + e.getMessage());
        }
    }


    public void write2Rides()
    {
        for (MeetingItem meetingItem : meetingItems) {

            meetingItem.saveMeetingItem2Redis();

        }
    }
    public static void main(String[] args) {
        RedisToos redisToos = RedisToos.getInstance();
        redisToos.getJedis().flushDB();
        ImportMeetingMenber importMeetingMenber = new ImportMeetingMenber("/Users/zgw/temp/poitest 1.xlsx");
        importMeetingMenber.MeetingMenberImages();
        importMeetingMenber.ImportMeetingMenberCells();
        importMeetingMenber.write2Rides();
//        importMeetingMenber.meetingItems.forEach(System.out::println);
        System.out.println("POIImages.getInstance().getImages().size() = " + POIImages.getInstance().getImages().size());
//
//        for (MeetingItem meetingItem : importMeetingMenber.meetingItems) {
//            System.out.println("name: " + meetingItem.getName() + " contents: " + meetingItem.getContents() + " dept: " + meetingItem.getDept() + " groupid: " + meetingItem.getGroupid() + " groupname: " + meetingItem.getGroupname() + " order: " + meetingItem.getOrder() + " image: " + meetingItem.getImage());
//            meetingItem.saveMeetingItem2Redis();
//
//        }

//        importMeetingMenber.writeToExcle("/Users/zgw/temp/poitest2.xlsx");

    }
}
